package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsLicenseInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.LicenseVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author hz
 * @since 2021-05-14
 */
public interface TsLicenseInfoMapper extends BaseMapper<TsLicenseInfo> {

    /**
     * 获取当年许可用水量
     * @return
     */
    @Select("SELECT sum(license_volume)/10000 as license FROM ts_license_info where available = 1")
    Double getLicenseVolume();

    /**
     *获取许可证信息
     * @param applicantName 用水户名称
     * @param license       许可证
     * @param start         开始时间
     * @param end           结束时间
     * @return
     */
    @Select("<script>" +
            "SELECT " +
            "li.id, " +
            "ai.applicant_id, " +
            "ai.applicant_name, " +
            "li.license, " +
            "li.start_time, " +
            "li.license_volume / 10000 as licenseVolume, " +
            "li.available, " +
            "td.department_id,\n" +
            "td.department_name\n" +
            "FROM \n" +
            " ts_license_info li\n" +
            "LEFT JOIN ts_applicant_info ai ON li.applicant_id = ai.applicant_id\n" +
            "LEFT JOIN ts_department td ON li.department_id = td.department_id" +
            "<where> " +
            "<if test='applicantName!=null'>" +
            "and ai.applicant_name like concat('%', #{applicantName}, '%')  " +
            "</if>" +
            "<if test='license!=null'>" +
            "and li.license like concat('%', #{license}, '%')  " +
            "</if>" +
            "<if test='start!=null'>" +
            "and li.start_time BETWEEN #{start} and #{end} " +
            "</if>" +
            "</where>" +
            "</script>")
    List<LicenseVo> getLicenseInfo(String applicantName, String license, String start, String end);
}
